{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a30a3fc9-85ee-41cd-851f-c035286045de",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from perspective.widget import PerspectiveWidget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b805dcc1-e4bd-4046-b966-602d46e9b2f7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (363, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>1</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:33:37&quot;</td><td>&quot;000900&quot;</td><td>&quot;现代投资&quot;</td><td>&quot;买入&quot;</td><td>4.05</td><td>34400.0</td><td>139320.0</td><td>22.29</td><td>0.0</td><td>1.39</td><td>0.0</td><td>-139342.29</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>2</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:34:24&quot;</td><td>&quot;601077&quot;</td><td>&quot;渝农商行&quot;</td><td>&quot;买入&quot;</td><td>3.65</td><td>38300.0</td><td>139795.0</td><td>22.37</td><td>0.0</td><td>1.38</td><td>0.0</td><td>-139818.75</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>4</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:37:25&quot;</td><td>&quot;601992&quot;</td><td>&quot;金隅集团&quot;</td><td>&quot;买入&quot;</td><td>2.59</td><td>54000.0</td><td>139860.0</td><td>22.38</td><td>0.0</td><td>1.42</td><td>0.0</td><td>-139883.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>5</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:38:16&quot;</td><td>&quot;002462&quot;</td><td>&quot;嘉事堂&quot;</td><td>&quot;买入&quot;</td><td>13.51</td><td>10400.0</td><td>140504.0</td><td>22.48</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-140526.48</td><td>0.00016</td><td>0.0</td><td>0.00001</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>359</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:31:53&quot;</td><td>&quot;002956&quot;</td><td>&quot;西麦食品&quot;</td><td>&quot;卖出&quot;</td><td>14.13</td><td>5000.0</td><td>70650.0</td><td>6.74</td><td>35.35</td><td>0.0</td><td>0.0</td><td>70607.91</td><td>0.000095</td><td>0.0005</td><td>0.0</td></tr><tr><td>360</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:39:57&quot;</td><td>&quot;603214&quot;</td><td>&quot;爱婴室&quot;</td><td>&quot;买入&quot;</td><td>15.84</td><td>3100.0</td><td>49104.0</td><td>5.0</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-49109.51</td><td>0.000102</td><td>0.0</td><td>0.00001</td></tr><tr><td>361</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:40:55&quot;</td><td>&quot;300132&quot;</td><td>&quot;青松股份&quot;</td><td>&quot;买入&quot;</td><td>5.21</td><td>9600.0</td><td>50016.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-50021.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr><tr><td>362</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:43:13&quot;</td><td>&quot;002492&quot;</td><td>&quot;恒基达鑫&quot;</td><td>&quot;买入&quot;</td><td>5.91</td><td>8400.0</td><td>49644.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49649.0</td><td>0.000101</td><td>0.0</td><td>0.0</td></tr><tr><td>363</td><td>&quot;海通两融&quot;</td><td>2023-10-31</td><td>&quot;09:44:45&quot;</td><td>&quot;002111&quot;</td><td>&quot;威海广泰&quot;</td><td>&quot;买入&quot;</td><td>9.24</td><td>5400.0</td><td>49896.0</td><td>5.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>-49901.0</td><td>0.0001</td><td>0.0</td><td>0.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (363, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬────────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额   ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---        ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64        ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪════════════╪══════════╪══════════╪══════════╡\n",
       "│ 1    ┆ 湘财     ┆ 2022-07-11 ┆ 09:33:37 ┆ … ┆ -139342.29 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 2    ┆ 湘财     ┆ 2022-07-11 ┆ 09:34:24 ┆ … ┆ -139818.75 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 3    ┆ 湘财     ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -139979.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 4    ┆ 湘财     ┆ 2022-07-11 ┆ 09:37:25 ┆ … ┆ -139883.8  ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 5    ┆ 湘财     ┆ 2022-07-11 ┆ 09:38:16 ┆ … ┆ -140526.48 ┆ 0.00016  ┆ 0.0      ┆ 0.00001  │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …          ┆ …        ┆ …        ┆ …        │\n",
       "│ 359  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:31:53 ┆ … ┆ 70607.91   ┆ 0.000095 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 360  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:39:57 ┆ … ┆ -49109.51  ┆ 0.000102 ┆ 0.0      ┆ 0.00001  │\n",
       "│ 361  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:40:55 ┆ … ┆ -50021.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "│ 362  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:43:13 ┆ … ┆ -49649.0   ┆ 0.000101 ┆ 0.0      ┆ 0.0      │\n",
       "│ 363  ┆ 海通两融 ┆ 2023-10-31 ┆ 09:44:45 ┆ … ┆ -49901.0   ┆ 0.0001   ┆ 0.0      ┆ 0.0      │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴────────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pl.read_parquet(\"stock_trades.parquet\")\n",
    "df = df.sort(\"交易日期\", \"交易时间\", \"证券代码\")\n",
    "df = df.with_columns(\n",
    "    pl.col(\"交易时间\").cast(pl.String), \n",
    "    手续费率=pl.col(\"手续费\") / pl.col(\"成交金额\"), \n",
    "    印花税率=pl.col(\"印花税\") / pl.col(\"成交金额\"), \n",
    "    过户费率=pl.col(\"过户费\") / pl.col(\"成交金额\"), \n",
    ")\n",
    "df = df.with_row_index(\"序号\", 1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "84060857-9d53-4441-938b-e9184f3d94cf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (2, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>券商</th><th>count</th></tr><tr><td>str</td><td>u32</td></tr></thead><tbody><tr><td>&quot;湘财&quot;</td><td>19</td></tr><tr><td>&quot;海通普通&quot;</td><td>33</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (2, 2)\n",
       "┌──────────┬───────┐\n",
       "│ 券商     ┆ count │\n",
       "│ ---      ┆ ---   │\n",
       "│ str      ┆ u32   │\n",
       "╞══════════╪═══════╡\n",
       "│ 湘财     ┆ 19    │\n",
       "│ 海通普通 ┆ 33    │\n",
       "└──────────┴───────┘"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(pl.col(\"手续费\") < 5)['券商'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "45566d34-56ac-475d-9084-55d9ba4fecdc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (53, 18)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>320</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:34:39&quot;</td><td>&quot;300642&quot;</td><td>&quot;透景生命&quot;</td><td>&quot;卖出&quot;</td><td>18.106</td><td>2500.0</td><td>45266.0</td><td>5.0</td><td>45.26</td><td>0.0</td><td>0.0</td><td>45215.74</td><td>0.00011</td><td>0.001</td><td>0.0</td></tr><tr><td>323</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:39:25&quot;</td><td>&quot;600333&quot;</td><td>&quot;长春燃气&quot;</td><td>&quot;卖出&quot;</td><td>5.04</td><td>3900.0</td><td>19656.0</td><td>5.0</td><td>19.64</td><td>0.23</td><td>0.0</td><td>19631.13</td><td>0.000254</td><td>0.000999</td><td>0.000012</td></tr><tr><td>325</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;10:03:59&quot;</td><td>&quot;600525&quot;</td><td>&quot;长园集团&quot;</td><td>&quot;买入&quot;</td><td>4.88</td><td>8500.0</td><td>41480.0</td><td>5.0</td><td>0.0</td><td>0.35</td><td>0.0</td><td>-41485.35</td><td>0.000121</td><td>0.0</td><td>0.000008</td></tr><tr><td>327</td><td>&quot;海通两融&quot;</td><td>2023-09-01</td><td>&quot;10:22:36&quot;</td><td>&quot;300889&quot;</td><td>&quot;爱克股份&quot;</td><td>&quot;卖出&quot;</td><td>15.22</td><td>1500.0</td><td>22830.0</td><td>5.0</td><td>11.42</td><td>0.0</td><td>0.0</td><td>22813.58</td><td>0.000219</td><td>0.0005</td><td>0.0</td></tr><tr><td>329</td><td>&quot;海通两融&quot;</td><td>2023-09-01</td><td>&quot;10:23:28&quot;</td><td>&quot;300889&quot;</td><td>&quot;爱克股份&quot;</td><td>&quot;卖出&quot;</td><td>15.21</td><td>1700.0</td><td>25857.0</td><td>5.0</td><td>12.92</td><td>0.0</td><td>0.0</td><td>25839.08</td><td>0.000193</td><td>0.0005</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>322</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:36:13&quot;</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;卖出&quot;</td><td>9.36</td><td>10600.0</td><td>99216.0</td><td>10.91</td><td>99.24</td><td>1.0</td><td>0.0</td><td>99104.85</td><td>0.00011</td><td>0.001</td><td>0.00001</td></tr><tr><td>319</td><td>&quot;海通两融&quot;</td><td>2023-08-09</td><td>&quot;10:12:16&quot;</td><td>&quot;600336&quot;</td><td>&quot;澳柯玛&quot;</td><td>&quot;买入&quot;</td><td>5.72</td><td>17400.0</td><td>99528.0</td><td>10.95</td><td>0.0</td><td>1.0</td><td>0.0</td><td>-99539.95</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>324</td><td>&quot;海通两融&quot;</td><td>2023-08-22</td><td>&quot;09:58:46&quot;</td><td>&quot;603626&quot;</td><td>&quot;科森科技&quot;</td><td>&quot;买入&quot;</td><td>6.35</td><td>15700.0</td><td>99695.0</td><td>10.97</td><td>0.0</td><td>1.02</td><td>0.0</td><td>-99706.99</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>316</td><td>&quot;海通两融&quot;</td><td>2023-08-04</td><td>&quot;10:07:37&quot;</td><td>&quot;603967&quot;</td><td>&quot;中创物流&quot;</td><td>&quot;买入&quot;</td><td>9.43</td><td>10600.0</td><td>99958.0</td><td>11.0</td><td>0.0</td><td>1.01</td><td>0.0</td><td>-99970.01</td><td>0.00011</td><td>0.0</td><td>0.00001</td></tr><tr><td>340</td><td>&quot;海通两融&quot;</td><td>2023-09-12</td><td>&quot;14:31:39&quot;</td><td>&quot;603626&quot;</td><td>&quot;科森科技&quot;</td><td>&quot;卖出&quot;</td><td>7.43</td><td>15700.0</td><td>116651.0</td><td>11.13</td><td>58.32</td><td>1.17</td><td>0.0</td><td>116580.38</td><td>0.000095</td><td>0.0005</td><td>0.00001</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (53, 18)\n",
       "┌──────┬──────────┬────────────┬──────────┬───┬───────────┬──────────┬──────────┬──────────┐\n",
       "│ 序号 ┆ 券商     ┆ 交易日期   ┆ 交易时间 ┆ … ┆ 发生金额  ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 │\n",
       "│ ---  ┆ ---      ┆ ---        ┆ ---      ┆   ┆ ---       ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ u32  ┆ str      ┆ date       ┆ str      ┆   ┆ f64       ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞══════╪══════════╪════════════╪══════════╪═══╪═══════════╪══════════╪══════════╪══════════╡\n",
       "│ 320  ┆ 海通两融 ┆ 2023-08-22 ┆ 09:34:39 ┆ … ┆ 45215.74  ┆ 0.00011  ┆ 0.001    ┆ 0.0      │\n",
       "│ 323  ┆ 海通两融 ┆ 2023-08-22 ┆ 09:39:25 ┆ … ┆ 19631.13  ┆ 0.000254 ┆ 0.000999 ┆ 0.000012 │\n",
       "│ 325  ┆ 海通两融 ┆ 2023-08-22 ┆ 10:03:59 ┆ … ┆ -41485.35 ┆ 0.000121 ┆ 0.0      ┆ 0.000008 │\n",
       "│ 327  ┆ 海通两融 ┆ 2023-09-01 ┆ 10:22:36 ┆ … ┆ 22813.58  ┆ 0.000219 ┆ 0.0005   ┆ 0.0      │\n",
       "│ 329  ┆ 海通两融 ┆ 2023-09-01 ┆ 10:23:28 ┆ … ┆ 25839.08  ┆ 0.000193 ┆ 0.0005   ┆ 0.0      │\n",
       "│ …    ┆ …        ┆ …          ┆ …        ┆ … ┆ …         ┆ …        ┆ …        ┆ …        │\n",
       "│ 322  ┆ 海通两融 ┆ 2023-08-22 ┆ 09:36:13 ┆ … ┆ 99104.85  ┆ 0.00011  ┆ 0.001    ┆ 0.00001  │\n",
       "│ 319  ┆ 海通两融 ┆ 2023-08-09 ┆ 10:12:16 ┆ … ┆ -99539.95 ┆ 0.00011  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 324  ┆ 海通两融 ┆ 2023-08-22 ┆ 09:58:46 ┆ … ┆ -99706.99 ┆ 0.00011  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 316  ┆ 海通两融 ┆ 2023-08-04 ┆ 10:07:37 ┆ … ┆ -99970.01 ┆ 0.00011  ┆ 0.0      ┆ 0.00001  │\n",
       "│ 340  ┆ 海通两融 ┆ 2023-09-12 ┆ 14:31:39 ┆ … ┆ 116580.38 ┆ 0.000095 ┆ 0.0005   ┆ 0.00001  │\n",
       "└──────┴──────────┴────────────┴──────────┴───┴───────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.filter(pl.col(\"券商\") == \"海通两融\").sort(\"手续费\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b1846c87-c56a-4e26-aab2-6aec481ac9a4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "178500d5416243839014312c6352f80b",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['序号', '券商', '交易日期', '交易时间', '证券代码', '证券名称', '买卖标志', '成交价格', …"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7c2d1624-5002-4364-8378-41c7e4423661",
   "metadata": {},
   "outputs": [],
   "source": [
    "d1 = df.join(\n",
    "    df.group_by(\"证券代码\", \"证券名称\")\n",
    "    .agg(\n",
    "        结余数量=(\n",
    "            pl.when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "            .then(-pl.col(\"成交数量\"))\n",
    "            .when(pl.col(\"买卖标志\") == \"买入\")\n",
    "            .then(pl.col(\"成交数量\"))\n",
    "            .sum()\n",
    "        ), \n",
    "    )\n",
    "    .filter(pl.col(\"结余数量\") < 0), \n",
    "    on=\"证券代码\", \n",
    "    how=\"anti\", \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f4a33f1e-6f89-4587-a137-72b18363a656",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2022, 7, 11)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_date = df[\"交易日期\"].min()\n",
    "start_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "ec8bbb5f-8a19-409c-b1c5-cd7eaf1f6c83",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.date(2023, 10, 31)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "end_date = df[\"交易日期\"].max()\n",
    "end_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "b53ecd82-758c-4975-bad4-89683a59cc0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (478, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th></tr><tr><td>date</td></tr></thead><tbody><tr><td>2022-07-11</td></tr><tr><td>2022-07-12</td></tr><tr><td>2022-07-13</td></tr><tr><td>2022-07-14</td></tr><tr><td>2022-07-15</td></tr><tr><td>&hellip;</td></tr><tr><td>2023-10-27</td></tr><tr><td>2023-10-28</td></tr><tr><td>2023-10-29</td></tr><tr><td>2023-10-30</td></tr><tr><td>2023-10-31</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (478, 1)\n",
       "┌────────────┐\n",
       "│ 日期       │\n",
       "│ ---        │\n",
       "│ date       │\n",
       "╞════════════╡\n",
       "│ 2022-07-11 │\n",
       "│ 2022-07-12 │\n",
       "│ 2022-07-13 │\n",
       "│ 2022-07-14 │\n",
       "│ 2022-07-15 │\n",
       "│ …          │\n",
       "│ 2023-10-27 │\n",
       "│ 2023-10-28 │\n",
       "│ 2023-10-29 │\n",
       "│ 2023-10-30 │\n",
       "│ 2023-10-31 │\n",
       "└────────────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k1 = pl.select(\n",
    "    日期=pl.date_range(start_date, end_date)\n",
    ")\n",
    "k1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f161f0c0-3bbd-483f-80c2-79cdbd6c7a77",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (152, 1)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;000096&quot;</td></tr><tr><td>&quot;000532&quot;</td></tr><tr><td>&quot;000559&quot;</td></tr><tr><td>&quot;000599&quot;</td></tr><tr><td>&quot;000655&quot;</td></tr><tr><td>&hellip;</td></tr><tr><td>&quot;688299&quot;</td></tr><tr><td>&quot;688321&quot;</td></tr><tr><td>&quot;688360&quot;</td></tr><tr><td>&quot;688393&quot;</td></tr><tr><td>&quot;688660&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (152, 1)\n",
       "┌──────────┐\n",
       "│ 证券代码 │\n",
       "│ ---      │\n",
       "│ str      │\n",
       "╞══════════╡\n",
       "│ 000096   │\n",
       "│ 000532   │\n",
       "│ 000559   │\n",
       "│ 000599   │\n",
       "│ 000655   │\n",
       "│ …        │\n",
       "│ 688299   │\n",
       "│ 688321   │\n",
       "│ 688360   │\n",
       "│ 688393   │\n",
       "│ 688660   │\n",
       "└──────────┘"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "k2 = df[\"证券代码\"].unique().sort().to_frame()\n",
    "k2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "5bacb8f5-0268-45e0-9b46-699b365ca388",
   "metadata": {},
   "outputs": [],
   "source": [
    "k = k1.join(k2, how=\"cross\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "b193653a-f68f-4007-8249-bb57ca4b3e6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "d2 = k.join(\n",
    "    d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    ").sort(\"日期\", \"证券代码\").with_columns(\n",
    "    结余数量=(\n",
    "        pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .otherwise(0).cum_sum().over(\"证券代码\")\n",
    "    ), \n",
    ").filter(\n",
    "    pl.col.结余数量 > 0\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bbd93f47-7495-452c-87a6-bc8c6936bafb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "d83ead078aa8495eaa57fae753e21688",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '证券代码', '序号', '券商', '交易时间', '证券名称', '买卖标志', '成交价格', '成…"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "3e9e38f2-49e0-407a-90eb-19331e99837e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tushare as ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "fd40f689-3e4c-40e9-a65f-89cfff4ef2c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "pro = ts.pro_api()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7c3babf8-d1c1-4482-b299-f5bdfbb691c2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20220711'"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f\"{start_date:%Y%m%d}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fc7ffa6b-1dd8-43f0-8433-5d0eb6bb1d17",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'20231031'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "format(end_date, \"%Y%m%d\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "9cc47231-ab64-45ba-b16d-ebebf2fc9f6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231031&quot;</td><td>14.75</td><td>14.9</td><td>14.59</td><td>14.7</td><td>14.75</td><td>-0.05</td><td>-0.339</td><td>65859.96</td><td>96984.271</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231030&quot;</td><td>13.88</td><td>14.89</td><td>13.88</td><td>14.75</td><td>13.93</td><td>0.82</td><td>5.8866</td><td>123932.16</td><td>180119.372</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231027&quot;</td><td>13.7</td><td>13.98</td><td>13.51</td><td>13.93</td><td>13.64</td><td>0.29</td><td>2.1261</td><td>35782.0</td><td>49386.168</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231026&quot;</td><td>13.49</td><td>13.68</td><td>13.4</td><td>13.64</td><td>13.62</td><td>0.02</td><td>0.1468</td><td>19215.0</td><td>26005.866</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20231025&quot;</td><td>13.65</td><td>13.77</td><td>13.58</td><td>13.62</td><td>13.67</td><td>-0.05</td><td>-0.3658</td><td>18484.0</td><td>25274.163</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220715&quot;</td><td>13.61</td><td>13.66</td><td>13.12</td><td>13.13</td><td>13.59</td><td>-0.46</td><td>-3.3848</td><td>32967.65</td><td>44114.064</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220714&quot;</td><td>13.54</td><td>13.75</td><td>13.5</td><td>13.59</td><td>13.54</td><td>0.05</td><td>0.3693</td><td>21967.0</td><td>29851.164</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220713&quot;</td><td>13.55</td><td>13.63</td><td>13.39</td><td>13.54</td><td>13.61</td><td>-0.07</td><td>-0.5143</td><td>22793.0</td><td>30714.624</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220712&quot;</td><td>13.65</td><td>13.69</td><td>13.41</td><td>13.61</td><td>13.65</td><td>-0.04</td><td>-0.293</td><td>29679.0</td><td>40146.31</td></tr><tr><td>&quot;002462.SZ&quot;</td><td>&quot;20220711&quot;</td><td>13.2</td><td>13.96</td><td>13.07</td><td>13.65</td><td>13.18</td><td>0.47</td><td>3.566</td><td>62827.0</td><td>85869.11</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 11)\n",
       "┌───────────┬────────────┬───────┬───────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open  ┆ high  ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---   ┆ ---   ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64   ┆ f64   ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪═══════╪═══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 002462.SZ ┆ 20231031   ┆ 14.75 ┆ 14.9  ┆ … ┆ -0.05  ┆ -0.339  ┆ 65859.96  ┆ 96984.271  │\n",
       "│ 002462.SZ ┆ 20231030   ┆ 13.88 ┆ 14.89 ┆ … ┆ 0.82   ┆ 5.8866  ┆ 123932.16 ┆ 180119.372 │\n",
       "│ 002462.SZ ┆ 20231027   ┆ 13.7  ┆ 13.98 ┆ … ┆ 0.29   ┆ 2.1261  ┆ 35782.0   ┆ 49386.168  │\n",
       "│ 002462.SZ ┆ 20231026   ┆ 13.49 ┆ 13.68 ┆ … ┆ 0.02   ┆ 0.1468  ┆ 19215.0   ┆ 26005.866  │\n",
       "│ 002462.SZ ┆ 20231025   ┆ 13.65 ┆ 13.77 ┆ … ┆ -0.05  ┆ -0.3658 ┆ 18484.0   ┆ 25274.163  │\n",
       "│ …         ┆ …          ┆ …     ┆ …     ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 002462.SZ ┆ 20220715   ┆ 13.61 ┆ 13.66 ┆ … ┆ -0.46  ┆ -3.3848 ┆ 32967.65  ┆ 44114.064  │\n",
       "│ 002462.SZ ┆ 20220714   ┆ 13.54 ┆ 13.75 ┆ … ┆ 0.05   ┆ 0.3693  ┆ 21967.0   ┆ 29851.164  │\n",
       "│ 002462.SZ ┆ 20220713   ┆ 13.55 ┆ 13.63 ┆ … ┆ -0.07  ┆ -0.5143 ┆ 22793.0   ┆ 30714.624  │\n",
       "│ 002462.SZ ┆ 20220712   ┆ 13.65 ┆ 13.69 ┆ … ┆ -0.04  ┆ -0.293  ┆ 29679.0   ┆ 40146.31   │\n",
       "│ 002462.SZ ┆ 20220711   ┆ 13.2  ┆ 13.96 ┆ … ┆ 0.47   ┆ 3.566   ┆ 62827.0   ┆ 85869.11   │\n",
       "└───────────┴────────────┴───────┴───────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq = pro.daily(\n",
    "    ts_code=\"002462.SZ\", \n",
    "    start_date=format(start_date, \"%Y%m%d\"), \n",
    "    end_date=format(end_date, \"%Y%m%d\"), \n",
    ")\n",
    "hq = pl.from_pandas(hq)\n",
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "664c0a80-5cb7-4f8f-99f8-bd3721b0b2d1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (149,)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>证券代码</th></tr><tr><td>str</td></tr></thead><tbody><tr><td>&quot;601077&quot;</td></tr><tr><td>&quot;002106&quot;</td></tr><tr><td>&quot;300385&quot;</td></tr><tr><td>&quot;300422&quot;</td></tr><tr><td>&quot;603878&quot;</td></tr><tr><td>&hellip;</td></tr><tr><td>&quot;600980&quot;</td></tr><tr><td>&quot;600597&quot;</td></tr><tr><td>&quot;002381&quot;</td></tr><tr><td>&quot;002225&quot;</td></tr><tr><td>&quot;300132&quot;</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (149,)\n",
       "Series: '证券代码' [str]\n",
       "[\n",
       "\t\"601077\"\n",
       "\t\"002106\"\n",
       "\t\"300385\"\n",
       "\t\"300422\"\n",
       "\t\"603878\"\n",
       "\t…\n",
       "\t\"600980\"\n",
       "\t\"600597\"\n",
       "\t\"002381\"\n",
       "\t\"002225\"\n",
       "\t\"300132\"\n",
       "]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1['证券代码'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "745e70f8-9b58-4a2a-a124-06bdd9e680eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "ts_codes = d1.select(\n",
    "    证券代码=(\n",
    "        pl.when(pl.col(\"证券代码\").str.head(1).is_in([\"0\", \"3\"]))\n",
    "        .then(pl.format(\"{}.SZ\", pl.col(\"证券代码\")))\n",
    "        .when(pl.col(\"证券代码\").str.head(1) == \"6\")\n",
    "        .then(pl.format(\"{}.SH\", pl.col(\"证券代码\")))\n",
    "    ), \n",
    ").to_series().unique().sort().to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "70452130-c563-42a2-9999-33060600c5ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tqdm.notebook import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "f01d313a-65f3-47f4-a26e-16192a130291",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5b58e8740ddc47f7a81907c50dd6d198",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "  0%|          | 0/149 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "hq = [\n",
    "    pl.from_pandas(\n",
    "        pro.daily(\n",
    "            ts_code=ts_code, \n",
    "            start_date=format(start_date, \"%Y%m%d\"), \n",
    "            end_date=format(end_date, \"%Y%m%d\"), \n",
    "        )\n",
    "    )\n",
    "    for ts_code in tqdm(ts_codes)\n",
    "] "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "7a4bb0d1-beda-439f-85d1-19fe9ef72f71",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "149"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "a2e48a7f-8e64-466a-b379-ef0bca8635a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.concat(hq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "15830623-e14a-47eb-a838-05018ed92d65",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (47_355, 11)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th></tr><tr><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;000096.SZ&quot;</td><td>&quot;20231031&quot;</td><td>8.95</td><td>9.25</td><td>8.88</td><td>8.95</td><td>8.84</td><td>0.11</td><td>1.2443</td><td>45191.22</td><td>40697.722</td></tr><tr><td>&quot;000096.SZ&quot;</td><td>&quot;20231030&quot;</td><td>8.72</td><td>8.94</td><td>8.68</td><td>8.84</td><td>8.78</td><td>0.06</td><td>0.6834</td><td>33567.85</td><td>29716.156</td></tr><tr><td>&quot;000096.SZ&quot;</td><td>&quot;20231027&quot;</td><td>8.97</td><td>8.98</td><td>8.75</td><td>8.78</td><td>8.94</td><td>-0.16</td><td>-1.7897</td><td>30035.5</td><td>26547.822</td></tr><tr><td>&quot;000096.SZ&quot;</td><td>&quot;20231026&quot;</td><td>9.12</td><td>9.13</td><td>8.83</td><td>8.94</td><td>9.06</td><td>-0.12</td><td>-1.3245</td><td>28794.0</td><td>25735.179</td></tr><tr><td>&quot;000096.SZ&quot;</td><td>&quot;20231025&quot;</td><td>9.04</td><td>9.2</td><td>8.92</td><td>9.06</td><td>8.99</td><td>0.07</td><td>0.7786</td><td>31960.0</td><td>29019.019</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;688660.SH&quot;</td><td>&quot;20220715&quot;</td><td>9.73</td><td>10.11</td><td>9.65</td><td>9.76</td><td>9.73</td><td>0.03</td><td>0.3083</td><td>338386.93</td><td>334715.635</td></tr><tr><td>&quot;688660.SH&quot;</td><td>&quot;20220714&quot;</td><td>9.67</td><td>9.84</td><td>9.55</td><td>9.73</td><td>9.67</td><td>0.06</td><td>0.6205</td><td>329943.3</td><td>319289.544</td></tr><tr><td>&quot;688660.SH&quot;</td><td>&quot;20220713&quot;</td><td>9.06</td><td>9.86</td><td>9.0</td><td>9.67</td><td>9.06</td><td>0.61</td><td>6.7329</td><td>550236.58</td><td>524088.481</td></tr><tr><td>&quot;688660.SH&quot;</td><td>&quot;20220712&quot;</td><td>8.99</td><td>9.39</td><td>8.89</td><td>9.06</td><td>9.04</td><td>0.02</td><td>0.2212</td><td>288562.46</td><td>264062.174</td></tr><tr><td>&quot;688660.SH&quot;</td><td>&quot;20220711&quot;</td><td>9.0</td><td>9.2</td><td>8.89</td><td>9.04</td><td>9.02</td><td>0.02</td><td>0.2217</td><td>166819.63</td><td>150747.732</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (47_355, 11)\n",
       "┌───────────┬────────────┬──────┬───────┬───┬────────┬─────────┬───────────┬────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ open ┆ high  ┆ … ┆ change ┆ pct_chg ┆ vol       ┆ amount     │\n",
       "│ ---       ┆ ---        ┆ ---  ┆ ---   ┆   ┆ ---    ┆ ---     ┆ ---       ┆ ---        │\n",
       "│ str       ┆ str        ┆ f64  ┆ f64   ┆   ┆ f64    ┆ f64     ┆ f64       ┆ f64        │\n",
       "╞═══════════╪════════════╪══════╪═══════╪═══╪════════╪═════════╪═══════════╪════════════╡\n",
       "│ 000096.SZ ┆ 20231031   ┆ 8.95 ┆ 9.25  ┆ … ┆ 0.11   ┆ 1.2443  ┆ 45191.22  ┆ 40697.722  │\n",
       "│ 000096.SZ ┆ 20231030   ┆ 8.72 ┆ 8.94  ┆ … ┆ 0.06   ┆ 0.6834  ┆ 33567.85  ┆ 29716.156  │\n",
       "│ 000096.SZ ┆ 20231027   ┆ 8.97 ┆ 8.98  ┆ … ┆ -0.16  ┆ -1.7897 ┆ 30035.5   ┆ 26547.822  │\n",
       "│ 000096.SZ ┆ 20231026   ┆ 9.12 ┆ 9.13  ┆ … ┆ -0.12  ┆ -1.3245 ┆ 28794.0   ┆ 25735.179  │\n",
       "│ 000096.SZ ┆ 20231025   ┆ 9.04 ┆ 9.2   ┆ … ┆ 0.07   ┆ 0.7786  ┆ 31960.0   ┆ 29019.019  │\n",
       "│ …         ┆ …          ┆ …    ┆ …     ┆ … ┆ …      ┆ …       ┆ …         ┆ …          │\n",
       "│ 688660.SH ┆ 20220715   ┆ 9.73 ┆ 10.11 ┆ … ┆ 0.03   ┆ 0.3083  ┆ 338386.93 ┆ 334715.635 │\n",
       "│ 688660.SH ┆ 20220714   ┆ 9.67 ┆ 9.84  ┆ … ┆ 0.06   ┆ 0.6205  ┆ 329943.3  ┆ 319289.544 │\n",
       "│ 688660.SH ┆ 20220713   ┆ 9.06 ┆ 9.86  ┆ … ┆ 0.61   ┆ 6.7329  ┆ 550236.58 ┆ 524088.481 │\n",
       "│ 688660.SH ┆ 20220712   ┆ 8.99 ┆ 9.39  ┆ … ┆ 0.02   ┆ 0.2212  ┆ 288562.46 ┆ 264062.174 │\n",
       "│ 688660.SH ┆ 20220711   ┆ 9.0  ┆ 9.2   ┆ … ┆ 0.02   ┆ 0.2217  ┆ 166819.63 ┆ 150747.732 │\n",
       "└───────────┴────────────┴──────┴───────┴───┴────────┴─────────┴───────────┴────────────┘"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c7389bda-fe11-432c-a276-4abd35a726da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "/c/Users/31776/repo/week08\n"
     ]
    }
   ],
   "source": [
    "!pwd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2650843e-70fa-4067-9d7d-7c4720e3c358",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq.write_parquet(\"daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "e0dac5f5-fe70-4096-a7e4-049f5f482357",
   "metadata": {},
   "outputs": [],
   "source": [
    "hq = pl.read_parquet(\"daily.parquet\")\n",
    "hq = hq.with_columns(\n",
    "    pl.col(\"ts_code\").str.head(6), \n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "770ad6e4-8da4-40b0-8cb6-8ee388a2ccdf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (358, 28)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>序号</th><th>券商</th><th>交易日期</th><th>交易时间</th><th>证券代码</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>open</th><th>high</th><th>low</th><th>close</th><th>pre_close</th><th>change</th><th>pct_chg</th><th>vol</th><th>amount</th><th>vratio</th></tr><tr><td>u32</td><td>str</td><td>date</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>237</td><td>&quot;湘财&quot;</td><td>2023-05-09</td><td>&quot;09:33:16&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;卖出&quot;</td><td>17.75</td><td>3000.0</td><td>53250.0</td><td>8.52</td><td>53.25</td><td>0.53</td><td>0.0</td><td>53187.7</td><td>0.00016</td><td>0.001</td><td>0.00001</td><td>17.77</td><td>17.94</td><td>17.5</td><td>17.56</td><td>17.63</td><td>-0.07</td><td>-0.3971</td><td>1.6276e6</td><td>2.8810e6</td><td>0.000018</td></tr><tr><td>48</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:19&quot;</td><td>&quot;300368&quot;</td><td>&quot;汇金股份&quot;</td><td>&quot;卖出&quot;</td><td>6.2</td><td>100.0</td><td>620.0</td><td>0.1</td><td>0.62</td><td>0.01</td><td>0.0</td><td>619.28</td><td>0.000161</td><td>0.001</td><td>0.000016</td><td>6.18</td><td>6.25</td><td>5.95</td><td>6.06</td><td>6.2</td><td>-0.14</td><td>-2.2581</td><td>49219.99</td><td>29911.915</td><td>0.00002</td></tr><tr><td>49</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:30:39&quot;</td><td>&quot;002996&quot;</td><td>&quot;顺博合金&quot;</td><td>&quot;卖出&quot;</td><td>13.64</td><td>100.0</td><td>1364.0</td><td>0.22</td><td>1.36</td><td>0.01</td><td>0.0</td><td>1362.42</td><td>0.000161</td><td>0.000997</td><td>0.000007</td><td>13.57</td><td>13.93</td><td>13.26</td><td>13.82</td><td>13.64</td><td>0.18</td><td>1.3196</td><td>38258.22</td><td>52122.119</td><td>0.000026</td></tr><tr><td>235</td><td>&quot;湘财&quot;</td><td>2023-04-25</td><td>&quot;13:35:07&quot;</td><td>&quot;601166&quot;</td><td>&quot;兴业银行&quot;</td><td>&quot;买入&quot;</td><td>17.15</td><td>3000.0</td><td>51450.0</td><td>8.23</td><td>0.0</td><td>0.51</td><td>0.0</td><td>-51458.74</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>16.96</td><td>17.29</td><td>16.96</td><td>17.27</td><td>16.93</td><td>0.34</td><td>2.0083</td><td>995234.22</td><td>1.7071e6</td><td>0.00003</td></tr><tr><td>46</td><td>&quot;湘财&quot;</td><td>2022-10-25</td><td>&quot;09:25:00&quot;</td><td>&quot;002998&quot;</td><td>&quot;优彩资源&quot;</td><td>&quot;卖出&quot;</td><td>6.73</td><td>100.0</td><td>673.0</td><td>0.11</td><td>0.67</td><td>0.01</td><td>0.0</td><td>672.22</td><td>0.000163</td><td>0.000996</td><td>0.000015</td><td>6.73</td><td>6.73</td><td>6.56</td><td>6.64</td><td>6.72</td><td>-0.08</td><td>-1.1905</td><td>16145.0</td><td>10687.614</td><td>0.000062</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>30</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:41:06&quot;</td><td>&quot;600231&quot;</td><td>&quot;凌钢股份&quot;</td><td>&quot;买入&quot;</td><td>2.2</td><td>62700.0</td><td>137940.0</td><td>22.07</td><td>0.0</td><td>1.29</td><td>0.0</td><td>-137963.36</td><td>0.00016</td><td>0.0</td><td>0.000009</td><td>2.21</td><td>2.22</td><td>2.15</td><td>2.2</td><td>2.21</td><td>-0.01</td><td>-0.4525</td><td>61371.2</td><td>13386.328</td><td>0.010217</td></tr><tr><td>3</td><td>&quot;湘财&quot;</td><td>2022-07-11</td><td>&quot;09:36:30&quot;</td><td>&quot;600894&quot;</td><td>&quot;广日股份&quot;</td><td>&quot;买入&quot;</td><td>6.54</td><td>21400.0</td><td>139956.0</td><td>22.39</td><td>0.0</td><td>1.41</td><td>0.0</td><td>-139979.8</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.57</td><td>6.57</td><td>6.49</td><td>6.51</td><td>6.57</td><td>-0.06</td><td>-0.9132</td><td>20737.5</td><td>13537.134</td><td>0.010319</td></tr><tr><td>142</td><td>&quot;湘财&quot;</td><td>2023-02-21</td><td>&quot;09:37:16&quot;</td><td>&quot;300385&quot;</td><td>&quot;雪浪环境&quot;</td><td>&quot;买入&quot;</td><td>6.5293</td><td>11500.0</td><td>75087.0</td><td>12.01</td><td>0.0</td><td>0.77</td><td>0.0</td><td>-75099.01</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>6.53</td><td>6.59</td><td>6.45</td><td>6.52</td><td>6.5</td><td>0.02</td><td>0.3077</td><td>11136.0</td><td>7250.686</td><td>0.010327</td></tr><tr><td>152</td><td>&quot;湘财&quot;</td><td>2023-03-01</td><td>&quot;09:31:46&quot;</td><td>&quot;688069&quot;</td><td>&quot;德林海环保&quot;</td><td>&quot;卖出&quot;</td><td>28.2838</td><td>2600.0</td><td>73538.0</td><td>11.77</td><td>73.58</td><td>0.77</td><td>0.0</td><td>73451.88</td><td>0.00016</td><td>0.001001</td><td>0.00001</td><td>28.52</td><td>28.86</td><td>28.27</td><td>28.67</td><td>28.54</td><td>0.13</td><td>0.4555</td><td>2416.06</td><td>6897.976</td><td>0.010761</td></tr><tr><td>27</td><td>&quot;湘财&quot;</td><td>2022-10-11</td><td>&quot;09:37:32&quot;</td><td>&quot;600261&quot;</td><td>&quot;阳光照明&quot;</td><td>&quot;买入&quot;</td><td>3.1134</td><td>44600.0</td><td>138857.0</td><td>22.22</td><td>0.0</td><td>1.33</td><td>0.0</td><td>-138880.55</td><td>0.00016</td><td>0.0</td><td>0.00001</td><td>3.14</td><td>3.15</td><td>3.06</td><td>3.13</td><td>3.11</td><td>0.02</td><td>0.6431</td><td>41397.03</td><td>12872.499</td><td>0.010774</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (358, 28)\n",
       "┌──────┬──────┬────────────┬──────────┬───┬─────────┬───────────┬───────────┬──────────┐\n",
       "│ 序号 ┆ 券商 ┆ 交易日期   ┆ 交易时间 ┆ … ┆ pct_chg ┆ vol       ┆ amount    ┆ vratio   │\n",
       "│ ---  ┆ ---  ┆ ---        ┆ ---      ┆   ┆ ---     ┆ ---       ┆ ---       ┆ ---      │\n",
       "│ u32  ┆ str  ┆ date       ┆ str      ┆   ┆ f64     ┆ f64       ┆ f64       ┆ f64      │\n",
       "╞══════╪══════╪════════════╪══════════╪═══╪═════════╪═══════════╪═══════════╪══════════╡\n",
       "│ 237  ┆ 湘财 ┆ 2023-05-09 ┆ 09:33:16 ┆ … ┆ -0.3971 ┆ 1.6276e6  ┆ 2.8810e6  ┆ 0.000018 │\n",
       "│ 48   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:19 ┆ … ┆ -2.2581 ┆ 49219.99  ┆ 29911.915 ┆ 0.00002  │\n",
       "│ 49   ┆ 湘财 ┆ 2022-10-25 ┆ 09:30:39 ┆ … ┆ 1.3196  ┆ 38258.22  ┆ 52122.119 ┆ 0.000026 │\n",
       "│ 235  ┆ 湘财 ┆ 2023-04-25 ┆ 13:35:07 ┆ … ┆ 2.0083  ┆ 995234.22 ┆ 1.7071e6  ┆ 0.00003  │\n",
       "│ 46   ┆ 湘财 ┆ 2022-10-25 ┆ 09:25:00 ┆ … ┆ -1.1905 ┆ 16145.0   ┆ 10687.614 ┆ 0.000062 │\n",
       "│ …    ┆ …    ┆ …          ┆ …        ┆ … ┆ …       ┆ …         ┆ …         ┆ …        │\n",
       "│ 30   ┆ 湘财 ┆ 2022-10-11 ┆ 09:41:06 ┆ … ┆ -0.4525 ┆ 61371.2   ┆ 13386.328 ┆ 0.010217 │\n",
       "│ 3    ┆ 湘财 ┆ 2022-07-11 ┆ 09:36:30 ┆ … ┆ -0.9132 ┆ 20737.5   ┆ 13537.134 ┆ 0.010319 │\n",
       "│ 142  ┆ 湘财 ┆ 2023-02-21 ┆ 09:37:16 ┆ … ┆ 0.3077  ┆ 11136.0   ┆ 7250.686  ┆ 0.010327 │\n",
       "│ 152  ┆ 湘财 ┆ 2023-03-01 ┆ 09:31:46 ┆ … ┆ 0.4555  ┆ 2416.06   ┆ 6897.976  ┆ 0.010761 │\n",
       "│ 27   ┆ 湘财 ┆ 2022-10-11 ┆ 09:37:32 ┆ … ┆ 0.6431  ┆ 41397.03  ┆ 12872.499 ┆ 0.010774 │\n",
       "└──────┴──────┴────────────┴──────────┴───┴─────────┴───────────┴───────────┴──────────┘"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d1.join(\n",
    "    hq, left_on=[\"交易日期\", \"证券代码\"], right_on=[\"trade_date\", \"ts_code\"], how=\"left\"\n",
    ").with_columns(\n",
    "    vratio=pl.col(\"成交数量\") / 100 / pl.col(\"vol\"), \n",
    ").sort(\"vratio\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "df71c624-6aee-4dd3-81d2-182d90ba8056",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (72_671, 19)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>日期</th><th>证券代码</th><th>序号</th><th>券商</th><th>交易时间</th><th>证券名称</th><th>买卖标志</th><th>成交价格</th><th>成交数量</th><th>成交金额</th><th>手续费</th><th>印花税</th><th>过户费</th><th>其他费</th><th>发生金额</th><th>手续费率</th><th>印花税率</th><th>过户费率</th><th>结余数量</th></tr><tr><td>date</td><td>str</td><td>u32</td><td>str</td><td>str</td><td>str</td><td>str</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>2022-07-11</td><td>&quot;000096&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000532&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000559&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000599&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2022-07-11</td><td>&quot;000655&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2023-10-31</td><td>&quot;688299&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688321&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688360&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688393&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>0.0</td></tr><tr><td>2023-10-31</td><td>&quot;688660&quot;</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>null</td><td>20200.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (72_671, 19)\n",
       "┌────────────┬──────────┬──────┬──────┬───┬──────────┬──────────┬──────────┬──────────┐\n",
       "│ 日期       ┆ 证券代码 ┆ 序号 ┆ 券商 ┆ … ┆ 手续费率 ┆ 印花税率 ┆ 过户费率 ┆ 结余数量 │\n",
       "│ ---        ┆ ---      ┆ ---  ┆ ---  ┆   ┆ ---      ┆ ---      ┆ ---      ┆ ---      │\n",
       "│ date       ┆ str      ┆ u32  ┆ str  ┆   ┆ f64      ┆ f64      ┆ f64      ┆ f64      │\n",
       "╞════════════╪══════════╪══════╪══════╪═══╪══════════╪══════════╪══════════╪══════════╡\n",
       "│ 2022-07-11 ┆ 000096   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000532   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000559   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000599   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2022-07-11 ┆ 000655   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ …          ┆ …        ┆ …    ┆ …    ┆ … ┆ …        ┆ …        ┆ …        ┆ …        │\n",
       "│ 2023-10-31 ┆ 688299   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688321   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688360   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688393   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 0.0      │\n",
       "│ 2023-10-31 ┆ 688660   ┆ null ┆ null ┆ … ┆ null     ┆ null     ┆ null     ┆ 20200.0  │\n",
       "└────────────┴──────────┴──────┴──────┴───┴──────────┴──────────┴──────────┴──────────┘"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d3 = (\n",
    "    k.join(\n",
    "    d1, left_on=[\"日期\", \"证券代码\"], right_on=[\"交易日期\", \"证券代码\"], how=\"left\"\n",
    ").sort(\"日期\", \"证券代码\").with_columns(\n",
    "    结余数量=(\n",
    "        pl.when(pl.col(\"买卖标志\") == \"买入\")\n",
    "        .then(pl.col(\"成交数量\"))\n",
    "        .when(pl.col(\"买卖标志\") == \"卖出\")\n",
    "        .then(-pl.col(\"成交数量\"))\n",
    "        .otherwise(0).cum_sum().over(\"证券代码\")\n",
    "        ), \n",
    "    )\n",
    ")\n",
    "d3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "fbceb50c-0427-4464-885a-4dd314f4a89b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (8, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>a</th><th>b</th></tr><tr><td>i64</td><td>i64</td></tr></thead><tbody><tr><td>2</td><td>2</td></tr><tr><td>8</td><td>8</td></tr><tr><td>null</td><td>1</td></tr><tr><td>null</td><td>1</td></tr><tr><td>1</td><td>1</td></tr><tr><td>null</td><td>9</td></tr><tr><td>9</td><td>9</td></tr><tr><td>null</td><td>null</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (8, 2)\n",
       "┌──────┬──────┐\n",
       "│ a    ┆ b    │\n",
       "│ ---  ┆ ---  │\n",
       "│ i64  ┆ i64  │\n",
       "╞══════╪══════╡\n",
       "│ 2    ┆ 2    │\n",
       "│ 8    ┆ 8    │\n",
       "│ null ┆ 1    │\n",
       "│ null ┆ 1    │\n",
       "│ 1    ┆ 1    │\n",
       "│ null ┆ 9    │\n",
       "│ 9    ┆ 9    │\n",
       "│ null ┆ null │\n",
       "└──────┴──────┘"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pl.DataFrame({\"a\": [2,  8, None, None, 1, None, 9, None]}).with_columns(\n",
    "    b = pl.col.a.fill_null(strategy=\"backward\")\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "1cdbb835-af6a-4be1-af49-34e8c6853c62",
   "metadata": {},
   "outputs": [],
   "source": [
    "d4 = d3.join(\n",
    "    hq, \n",
    "    left_on=[\"日期\", \"证券代码\"], \n",
    "    right_on=[\"trade_date\", \"ts_code\"], \n",
    "    how=\"left\", \n",
    ").sort(\"证券代码\", \"日期\").with_columns(\n",
    "    close=pl.col(\"close\").fill_null(strategy=\"forward\").over(\"证券代码\")\n",
    ").with_columns(持股市值=pl.col(\"结余数量\") * pl.col(\"close\")).group_by(\"日期\").agg(\n",
    "    pl.col(\"持股市值\").sum(), \n",
    "    pl.col(\"发生金额\").sum(), \n",
    ").sort(\"日期\").with_columns(\n",
    "    转账金额=pl.when(pl.int_range(0, pl.len()) == 0).then(100_0000).otherwise(0),\n",
    ").with_columns(\n",
    "    现金余额=(pl.col(\"转账金额\") + pl.col(\"发生金额\")).cum_sum(), \n",
    ").with_columns(\n",
    "    总资产=pl.col(\"持股市值\") + pl.col(\"现金余额\"))\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "7cd5afed-9f26-4f3d-86f0-91042926d104",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "932d5394ca3b4805ac1e41ee623416ee",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '持股市值', '发生金额', '转账金额', '现金余额', '总资产'], table_name='0.…"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "552a967c-1f08-4766-9352-723eb6d4fed6",
   "metadata": {},
   "outputs": [],
   "source": [
    "ihq = pro.index_daily(\n",
    "    ts_code=\"000300.SH\", \n",
    "    start_date=format(start_date, \"%Y%m%d\"), \n",
    "    end_date=format(end_date, \"%Y%m%d\"), \n",
    "    fields=\"ts_code, trade_date, pct_chg\", \n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "c8c7bb2f-ace0-4975-af0e-e5a2beec1933",
   "metadata": {},
   "outputs": [],
   "source": [
    "pl.from_pandas(ihq).write_parquet(\"index_daily.parquet\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "0b23d36c-267c-45c9-a922-d2c608015f7c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (318, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ts_code</th><th>trade_date</th><th>pct_chg</th><th>car</th><th>沪深300</th></tr><tr><td>str</td><td>date</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;000300.SH&quot;</td><td>2022-07-11</td><td>0.983254</td><td>0.983254</td><td>983254.0</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-12</td><td>0.990585</td><td>0.973997</td><td>973996.66359</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-13</td><td>1.001818</td><td>0.975767</td><td>975767.389524</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-14</td><td>1.000142</td><td>0.975906</td><td>975905.948494</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2022-07-15</td><td>0.982983</td><td>0.959299</td><td>959298.956968</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-25</td><td>1.004969</td><td>0.791288</td><td>791288.453778</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-26</td><td>1.002764</td><td>0.793476</td><td>793475.575065</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-27</td><td>1.013727</td><td>0.804368</td><td>804367.614284</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-30</td><td>1.006003</td><td>0.809196</td><td>809196.233072</td></tr><tr><td>&quot;000300.SH&quot;</td><td>2023-10-31</td><td>0.996856</td><td>0.806652</td><td>806652.120115</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (318, 5)\n",
       "┌───────────┬────────────┬──────────┬──────────┬───────────────┐\n",
       "│ ts_code   ┆ trade_date ┆ pct_chg  ┆ car      ┆ 沪深300       │\n",
       "│ ---       ┆ ---        ┆ ---      ┆ ---      ┆ ---           │\n",
       "│ str       ┆ date       ┆ f64      ┆ f64      ┆ f64           │\n",
       "╞═══════════╪════════════╪══════════╪══════════╪═══════════════╡\n",
       "│ 000300.SH ┆ 2022-07-11 ┆ 0.983254 ┆ 0.983254 ┆ 983254.0      │\n",
       "│ 000300.SH ┆ 2022-07-12 ┆ 0.990585 ┆ 0.973997 ┆ 973996.66359  │\n",
       "│ 000300.SH ┆ 2022-07-13 ┆ 1.001818 ┆ 0.975767 ┆ 975767.389524 │\n",
       "│ 000300.SH ┆ 2022-07-14 ┆ 1.000142 ┆ 0.975906 ┆ 975905.948494 │\n",
       "│ 000300.SH ┆ 2022-07-15 ┆ 0.982983 ┆ 0.959299 ┆ 959298.956968 │\n",
       "│ …         ┆ …          ┆ …        ┆ …        ┆ …             │\n",
       "│ 000300.SH ┆ 2023-10-25 ┆ 1.004969 ┆ 0.791288 ┆ 791288.453778 │\n",
       "│ 000300.SH ┆ 2023-10-26 ┆ 1.002764 ┆ 0.793476 ┆ 793475.575065 │\n",
       "│ 000300.SH ┆ 2023-10-27 ┆ 1.013727 ┆ 0.804368 ┆ 804367.614284 │\n",
       "│ 000300.SH ┆ 2023-10-30 ┆ 1.006003 ┆ 0.809196 ┆ 809196.233072 │\n",
       "│ 000300.SH ┆ 2023-10-31 ┆ 0.996856 ┆ 0.806652 ┆ 806652.120115 │\n",
       "└───────────┴────────────┴──────────┴──────────┴───────────────┘"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ihq = pl.read_parquet(\"index_daily.parquet\")\n",
    "ihq = (ihq.with_columns(\n",
    "    pl.col(\"trade_date\").str.to_date(\"%Y%m%d\"), \n",
    "    pl.col(\"pct_chg\") / 100 + 1, \n",
    ").sort(\"trade_date\").with_columns(\n",
    "    car=pl.col(\"pct_chg\").cum_prod(), \n",
    ")\n",
    ".with_columns(\n",
    "    沪深300=pl.col(\"car\") * 100_0000,\n",
    ")\n",
    "      )\n",
    "ihq"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "25a1d8f4-49bf-4c75-bd82-0adc854dec45",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "e76962f165f64d3e8b23b10e768ff599",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['ts_code', 'trade_date', 'pct_chg', 'car', '沪深300'], table_n…"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(ihq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "10af377e-9ecb-4001-9c1a-37e8ebe42c65",
   "metadata": {},
   "outputs": [],
   "source": [
    "d5 = d4.join(ihq, left_on=\"日期\", right_on=\"trade_date\")\n",
    "d5 = d5.unpivot(on=[\"总资产\", \"沪深300\"], index=\"日期\", variable_name=\"资产类型\", value_name=\"财富\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "55f86102-f436-4872-b40b-f6a3a6009a4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "86bd49504c3241e485b538624eeaf82f",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "PerspectiveWidget(binding_mode='server', columns=['日期', '资产类型', '财富'], table_name='0.9695000141981474', theme=…"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "PerspectiveWidget(d5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "018a43c9-7dec-4b4d-b62b-2759078f966e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
